﻿Imports System.Data
Imports System.Configuration
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports TepcelDominio
Imports TepcelDAL.DBManager

Public Class UbigeoDAL : Inherits DBManager
    Public Function NuevoUbigeo(ByVal Ubigeo As Ubigeo) As String
        Dim comando As SqlCommand = MyBase.CreateSelectCommand("SPInsertUbigeo")

        comando.Parameters.Add("@codigoUbigeo", SqlDbType.Char, 5).Value = Ubigeo.codigoUbigeo
        comando.Parameters.Add("@nombreUbigeo", SqlDbType.VarChar, 60).Value = Ubigeo.nombreUbigeo
        comando.Parameters.Add("@dependenciaUbigeo", SqlDbType.Char, 5)

        comando.Parameters("@codigoUbigeo").Direction = ParameterDirection.InputOutput

        If Ubigeo.dependenciaUbigeo Is Nothing Then
            comando.Parameters("@dependenciaUbigeo").Value = ""
        Else
            If Ubigeo.dependenciaUbigeo.dependenciaUbigeo Is Nothing Then
                comando.Parameters("@dependenciaUbigeo").Value = Ubigeo.dependenciaUbigeo.codigoUbigeo
            Else
                comando.Parameters("@dependenciaUbigeo").Value = Ubigeo.dependenciaUbigeo.dependenciaUbigeo.codigoUbigeo
            End If
        End If

        MyBase.ExecuteQuery(comando)
        Ubigeo.codigoUbigeo = comando.Parameters("@codigoUbigeo").Value

        Return Ubigeo.codigoUbigeo

    End Function

    Public Function GetByIdFromUbigeo(ByVal codigoUbigeo As String) As Ubigeo
        Dim comando As SqlCommand = MyBase.CreateSelectCommand("GetByIdFromUbigeo")
        comando.Parameters.Add("@codigoUbigeo", SqlDbType.Char, 5).Value = codigoUbigeo
        Return SelectObjectFactory(comando, 3).Item(0)
    End Function

    Public Function GetAllFromUbigeo(ByVal tipoUbigeo As Integer) As List(Of Ubigeo)
        Dim comando As SqlCommand = MyBase.CreateSelectCommand("GetAllFromUbigeo")

        comando.Parameters.Add("@tipoUbigeo", SqlDbType.Int).Value = tipoUbigeo

        Return SelectObjectFactory(comando, tipoUbigeo)
    End Function

    Public Function SearchFromUbigeo(ByVal texto As String, ByVal tipoUbigeo As Integer) As List(Of Ubigeo)
        Dim comando As SqlCommand = MyBase.CreateSelectCommand("searchUbigeo")
        comando.Parameters.Add("@tipoUbigeo", SqlDbType.Int).Value = tipoUbigeo
        comando.Parameters.Add("@texto", SqlDbType.VarChar, 200).Value = texto
        Return SelectObjectFactory(comando, tipoUbigeo)
    End Function

    Public Function DeleteUbigeo(ByVal codigoUbigeo As String) As Boolean
        Dim correcto As Boolean = False
        Dim comando As SqlCommand = MyBase.CreateSelectCommand("DeleteUbigeo")

        comando.Parameters.Add("@codigoUbigeo", SqlDbType.Char, 5).Value = codigoUbigeo

        If MyBase.ExecuteQuery(comando) > 0 Then
            correcto = True
        End If
        Return correcto
    End Function

    Private Function SelectObjectFactory(ByVal comando As SqlCommand, ByVal tipoUbigeo As Integer) As List(Of Ubigeo)
        Dim Lista As New List(Of Ubigeo)
        Using reader As SqlDataReader = MyBase.ExecuteReader(comando)
            While reader.Read
                Dim Ubigeo As New Ubigeo

                Select Case tipoUbigeo
                    Case 1
                        Ubigeo.codigoUbigeo = reader.GetString(0)
                        Ubigeo.nombreUbigeo = reader.GetString(1)
                        Ubigeo.dependenciaUbigeo = Nothing
                    Case 2
                        Ubigeo.codigoUbigeo = reader.GetString(0)
                        Ubigeo.nombreUbigeo = reader.GetString(1)
                        Ubigeo.dependenciaUbigeo = New Ubigeo
                        Ubigeo.dependenciaUbigeo.codigoUbigeo = reader.GetString(2)
                        Ubigeo.dependenciaUbigeo.nombreUbigeo = reader.GetString(3)
                        Ubigeo.dependenciaUbigeo.dependenciaUbigeo = Nothing
                    Case 3
                        Ubigeo.codigoUbigeo = reader.GetString(0)
                        Ubigeo.nombreUbigeo = reader.GetString(1)
                        Ubigeo.dependenciaUbigeo = New Ubigeo
                        Ubigeo.dependenciaUbigeo.codigoUbigeo = reader.GetString(2)
                        Ubigeo.dependenciaUbigeo.nombreUbigeo = reader.GetString(3)
                        Ubigeo.dependenciaUbigeo.dependenciaUbigeo = New Ubigeo
                        Ubigeo.dependenciaUbigeo.dependenciaUbigeo.codigoUbigeo = reader.GetString(4)
                        Ubigeo.dependenciaUbigeo.dependenciaUbigeo.nombreUbigeo = reader.GetString(5)
                        Ubigeo.dependenciaUbigeo.dependenciaUbigeo.dependenciaUbigeo = Nothing
                End Select
                Lista.Add(Ubigeo)
                Ubigeo = Nothing
            End While
        End Using
        Return Lista
    End Function

End Class
